﻿Imports Microsoft.SqlServer.Management.Common
Imports Microsoft.SqlServer.Management.Smo
Imports System.Collections.Specialized
Imports System.Data.SqlClient

Public Class SMOHelper

#Region "Declaration"
    Private Shared m_Server As Server
    Private Shared m_lstServer As Dictionary(Of String, SMOHelper)
    Private Shared m_SMOHelper As SMOHelper
    Private m_strUserID As String = String.Empty
    Private m_strServerName As String = String.Empty
#End Region

#Region "Constructor"

    Public Sub New()
        m_Server = New Server()
    End Sub

    Private Sub New(ByVal _server As Server)
        m_Server = _server
        AddHandler m_Server.ConnectionContext.RemoteLoginFailed, AddressOf ConnectionContext_RemoteLoginFailed
        If m_lstServer Is Nothing Then
            m_lstServer = New Dictionary(Of String, SMOHelper)
        End If

        m_lstServer.Add(_server.InstanceName, m_SMOHelper)
    End Sub

    Private Sub ConnectionContext_RemoteLoginFailed(ByVal sender As Object, ByVal e As ServerMessageEventArgs)
        RemoveHandler DirectCast(sender, ServerConnection).RemoteLoginFailed, AddressOf ConnectionContext_RemoteLoginFailed

    End Sub

#End Region

#Region "Property"

    Public ReadOnly Property UserID() As String
        Get
            If m_Server IsNot Nothing Then
                m_strUserID = m_Server.ConnectionContext.ConnectAsUserName
            End If

            Return m_strUserID
        End Get
    End Property

    Public ReadOnly Property ServerName() As String
        Get
            If m_Server IsNot Nothing Then
                m_strServerName = m_Server.ConnectionContext.ServerInstance
            End If

            Return m_strServerName
        End Get
    End Property

    Public Shared Property Current() As SMOHelper
        Get
            Return m_SMOHelper
        End Get
        Set(ByVal value As SMOHelper)
            m_SMOHelper = value
        End Set
    End Property

#End Region

#Region "Method"

    Public Shared Sub Connect(ByVal _instance As String, ByVal _username As String, ByVal _password As String, ByVal _isSecure As Boolean)
        m_Server = New Server()
        With m_Server.ConnectionContext
            If _isSecure Then
                .LoginSecure = True
            Else
                .LoginSecure = False
                .Login = _username
                .Password = _password
            End If
            .ServerInstance = _instance
            .Connect()
        End With
        m_SMOHelper = New SMOHelper(m_Server)
    End Sub

    Public Function LoadBackupFile(ByVal fileName As String) As DataTable
        Dim dtResult As New DataTable
        Dim oRestore As New Restore

        With oRestore
            .Devices.AddDevice(fileName, DeviceType.File)
            dtResult = .ReadFileList(m_Server)
        End With

        Return dtResult
    End Function

    Public Function Databases() As DatabaseCollection
        If m_Server IsNot Nothing Then
            Return m_Server.Databases
        End If

        Return Nothing
    End Function

    Public Sub BackupDatabase(ByVal databaseName As String, ByVal fileName As String, Optional ByVal BackupCompletedCallback As ServerMessageEventHandler = Nothing)
        Dim oBackup As New Backup

        With oBackup
            .Database = databaseName
            .Action = BackupActionType.Database
            .Devices.AddDevice(fileName, DeviceType.File)
            .Initialize = True
            .Checksum = True
            .ContinueAfterError = True
            .Incremental = False
            .LogTruncation = BackupTruncateLogType.Truncate
            .BackupSetName = databaseName
            .BackupSetDescription = databaseName & " Database backup"
            .SqlBackup(m_Server)
            If BackupCompletedCallback IsNot Nothing Then
                AddHandler .Complete, BackupCompletedCallback
            End If
        End With
    End Sub

    Public Sub RestoreDatabase(ByVal databaseName As String, ByVal Path As String, ByVal fileName As String, Optional ByVal RestoreCompleteCallback As ServerMessageEventHandler = Nothing)
        Dim oRestore As New Restore
        Dim dt As DataTable
        With oRestore
            .Database = databaseName
            .Action = RestoreActionType.Database
            .ReplaceDatabase = True
            .Devices.AddDevice(fileName, DeviceType.File)
            '.FileNumber = fileNumber
            '.PercentCompleteNotification = 10
            dt = .ReadFileList(m_Server)
            Dim dr() As DataRow = dt.Select()
            Dim dbfile As String = dr(0).Item("LogicalName")
            Dim logfile As String = dr(1).Item("LogicalName")
            Dim snewdbfileName As String = Path & "\" & databaseName & ".smd"
            Dim snewlogfileName As String = Path & "\" & databaseName & "_log.sld"
            If Path <> "" Then
                .RelocateFiles.Add(New RelocateFile(dbfile, snewdbfileName))
                .RelocateFiles.Add(New RelocateFile(logfile, snewlogfileName))
            End If
            .SqlRestore(m_Server)
        End With
        If RestoreCompleteCallback IsNot Nothing Then
            AddHandler oRestore.Complete, RestoreCompleteCallback
        End If
    End Sub

    Public Function CheckExistDatabase(ByVal dbName As String) As Boolean
        If m_Server.Databases(dbName) IsNot Nothing Then
            Return True
        End If
        Return False
    End Function

    Public Sub DetachDatabase(ByVal dbName As String)
        Try
            m_Server.DetachDatabase(dbName, False)
        Catch ex As Exception
            Throw
        End Try
    End Sub

    Public Sub DeleteDatabase(ByVal dbName As String)
        If String.IsNullOrEmpty(dbName) Then
            Throw New NullReferenceException("Tên Database không được để trống.")
        End If

        Try
            If m_Server.Databases(dbName) IsNot Nothing Then
                m_Server.KillAllProcesses(dbName)
                m_Server.KillDatabase(dbName)
            End If
        Catch ex As SmoException
            Throw New SmoException()
        End Try
    End Sub

    Public Function AttachDatabase(ByVal dbName As String, ByVal fileName As String) As Boolean
        Dim bExist As Boolean = False
        Try
            Dim ofile As New StringCollection
            ofile.Add(fileName)
            m_Server.AttachDatabase(dbName, ofile)
            bExist = True
        Catch ex As Exception
            bExist = False
        End Try
        Return bExist
    End Function

    Public Function ListServer() As DataTable
        Dim dt As DataTable = SmoApplication.EnumAvailableSqlServers()
        Return dt
    End Function

    Public Function RunScript(ByVal script As String) As Integer
        Return m_Server.ConnectionContext.ExecuteNonQuery(script)
    End Function

    'Public Sub Eventds()

    '    'Connect to the local, default instance of SQL Server.
    '    Dim srv As Server
    '    srv = New Server
    '    'Reference the AdventureWorks2012 database.
    '    Dim db As Database
    '    db = srv.Databases("AdventureWorks2012")
    '    'Create a database event set that contains the CreateTable event only.
    '    Dim databaseCreateEventSet As New DatabaseEventSet
    '    databaseCreateEventSet.CreateTable = True
    '    'Create a server event handler and set it to the first event handler subroutine.
    '    Dim serverCreateEventHandler As ServerEventHandler
    '    serverCreateEventHandler = New ServerEventHandler(AddressOf MyCreateEventHandler)
    '    'Subscribe to the first server event handler when a CreateTable event occurs.
    '    db.Events.SubscribeToEvents(databaseCreateEventSet, serverCreateEventHandler)
    '    'Create a database event set that contains the DropTable event only.
    '    Dim databaseDropEventSet As New DatabaseEventSet
    '    databaseDropEventSet.DropTable = True
    '    'Create a server event handler and set it to the second event handler subroutine.
    '    Dim serverDropEventHandler As ServerEventHandler
    '    serverDropEventHandler = New ServerEventHandler(AddressOf MyDropEventHandler)
    '    'Subscribe to the second server event handler when a DropTable event occurs.
    '    db.Events.SubscribeToEvents(databaseDropEventSet, serverDropEventHandler)
    '    'Start event handling.
    '    db.Events.StartEvents()
    '    'Create a table on the database.
    '    Dim tb As Table
    '    tb = New Table(db, "Test_Table")
    '    Dim mycol1 As Column
    '    mycol1 = New Column(tb, "Name", DataType.NChar(50))
    '    mycol1.Collation = "Latin1_General_CI_AS"
    '    mycol1.Nullable = True
    '    tb.Columns.Add(mycol1)
    '    tb.Create()
    '    'Remove the table.
    '    tb.Drop()
    '    'Wait until the events have occured.
    '    Dim x As Integer
    '    Dim y As Integer
    '    For x = 1 To 1000000000
    '        y = x * 2
    '    Next
    '    'Stop event handling.
    '    db.Events.StopEvents()
    'End Sub

    Public Shared Function SelectServer(ByVal serverName As String) As SMOHelper
        If m_lstServer IsNot Nothing AndAlso m_lstServer.Count > 0 Then
            If m_lstServer.ContainsKey(serverName) Then
                Return m_lstServer(serverName)
            End If
        End If

        Return Nothing
    End Function

    Public Sub Disconnect()
        If m_Server IsNot Nothing OrElse m_Server.ConnectionContext.IsOpen Then
            m_Server.ConnectionContext.Disconnect()
            m_lstServer.Remove(m_Server.InstanceName)
            m_Server = Nothing
        End If
    End Sub

#End Region

#Region "Event"

#End Region

End Class
